using System;
using System.Data;
using System.Data.SqlClient;

public class CommandExample {

    public static void Main() {

        // Create a new SqlConnection object.
        using (SqlConnection con = new SqlConnection()) {
            decimal meanValue, maxValue;
            // Configure the SqlConnection object's connection string.
            con.ConnectionString = "Data Source = localhost;" + 
                "Database = BestellungenDB; Integrated Security=SSPI";

            // Open the Database connection and execute the 
            // commands through the connection.
            con.Open();

            ExecuteTeuereProdukte(con, "Hardware", 150.00m);
            Console.ReadLine();
            Console.WriteLine("\n \n");
 
            ExecuteProductGroupAnalysis(con, "Hardw%", out meanValue, out maxValue);
            Console.WriteLine("Mittelwert beim Preis: " + meanValue);
            Console.WriteLine("Maximaler Preis: " + maxValue);

           // Wait to continue.
            Console.ReadLine();
         }

     }


    public static void ExecuteTeuereProdukte(SqlConnection con, string gruppe, decimal refPreis)
    {

        // Create and configure a new command.
        SqlCommand com = con.CreateCommand();
        com.CommandType = CommandType.StoredProcedure;
        com.CommandText = "TeuereProdukte";
        com.Parameters.Add("@productGroup", SqlDbType.VarChar).Value = gruppe;
        com.Parameters.Add("@refPreis", SqlDbType.Decimal).Value = refPreis;
        // Execute the command and process the results
        using (IDataReader reader = com.ExecuteReader())
        {

            Console.WriteLine("Die teueren Produkte in der Produktgruppe "+gruppe+ ": ");

            while (reader.Read())
            {

                // Display the product details.
                Console.WriteLine("  {0} = {1}",
                    reader["Produktname"],
                    reader["Preis"]);
            }
        }
    }

    public static void ExecuteProductGroupAnalysis(SqlConnection con, string group, out decimal meanValue, out decimal maxValue)
    {

        // Create and configure a new command.

        SqlCommand com = con.CreateCommand();
        com.CommandType = CommandType.StoredProcedure;
        com.CommandText = "AnalysiereProduktgruppe";

        com.Parameters.Add("@productGroup", SqlDbType.VarChar).Value = group;
    
        SqlParameter parameterMittlererPreis = new SqlParameter("@MittlererPreis", SqlDbType.Int);
        parameterMittlererPreis.Direction = ParameterDirection.Output;
        com.Parameters.Add(parameterMittlererPreis);

        com.Parameters.Add("@MaximalerPreis", SqlDbType.Int).Direction = ParameterDirection.Output;
        
        SqlDataReader dr = com.ExecuteReader();
        dr.Close();

        meanValue = (decimal)((int) com.Parameters["@MittlererPreis"].Value)/100;
        maxValue = (decimal)((int) com.Parameters["@MaximalerPreis"].Value)/100;
        
        
    }
  
   

    
}
